package database;

import graphing.AnalysisController;
import graphing.RunDataResult;
import graphing.RunnerWithResult;

import java.io.File;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.Set;
import java.util.Vector;

import model.Cohort;
import model.Runner;
import parsing.Indices2010;
import parsing.Indices2011;
import parsing.Indices2012;
import parsing.RaceResultParsingInformation;
import utility.Utility;

public class RaceDataLoader {

	private Connection databaseConnection ; 
	private int yearRun ; 
	private RunDataResult fullResults ; 
	private String raceName ; 
	private int raceDistance ;
	Map<String, Runner> allPeopleEver ; 
	Map<String, Cohort> cohortMap ; 
	
	public static void main(String[] arguments) {
		Properties userProperties = Utility.getProperties() ; 
		String localPath = userProperties.getProperty("rootDirectory") ;
		//		String localPath = "C:\\Users\\KOoba\\workspace\\svn\\" ; // WARNING this has to change with the machine running the program.
		String dataPath = localPath + "\\RunningData\\data\\" ;
		// Below here we'll want some sort of loop to cover all years.
		ArrayList<RaceResultParsingInformation> allData = setRacesToLoad(); 
		boolean isATurkeyTrot = true ; 
		for (Iterator<RaceResultParsingInformation> iterator = allData.iterator(); iterator.hasNext();) {
			RaceResultParsingInformation raceResultParsingInformation = (RaceResultParsingInformation) iterator.next();
			loadRace(dataPath, isATurkeyTrot, raceResultParsingInformation);
		}
	}

	private static ArrayList<RaceResultParsingInformation> setRacesToLoad() {
		ArrayList<RaceResultParsingInformation> allData = new ArrayList<RaceResultParsingInformation>() ;
		allData.add(Indices2010.indices2010) ;
		allData.add(Indices2011.indices2011) ;
		allData.add(Indices2012.indices2012) ;
		return allData;
	}

	private static void loadRace(String dataPath, boolean isATurkeyTrot,
			RaceResultParsingInformation raceResultParsingInformation) {
		File dataFile = new File(dataPath + raceResultParsingInformation.getFileNameOfData()) ;
		Vector<String> fileContents = AnalysisController.importFile(dataFile);
		RunDataResult fullResults = AnalysisController.parseFile(fileContents, raceResultParsingInformation) ;
		int yearRun = raceResultParsingInformation.getYearOfRace() ; 
		RaceDataLoader raceDataLoader = new RaceDataLoader(yearRun, isATurkeyTrot, fullResults) ;
		raceDataLoader.loadRaceToDatabase() ;
	}

	private void loadRaceToDatabase() {
		this.databaseConnection = Database.fetchMySqlConnection(Utility.getProperties()) ;
		int raceId = loadRaceInformation() ; 
		loadCohorts(raceId) ; 
		loadRunnersAndResults(raceId) ; 
	}

	private void loadRunnersAndResults(int raceId) {
		allPeopleEver = fetchAllPeopleInDatabase() ; 
		List<RunnerWithResult> allRunnersAndResults =  this.fullResults.getRunners() ;
		String[] sqlBatch = new String[allRunnersAndResults.size() + 1] ; 
		for (int i = 0; i < allRunnersAndResults.size() ; i++) {
			RunnerWithResult runnerWithResult = allRunnersAndResults.get(i) ;
			Runner person = runnerWithResult.getPerson() ; 
			int personId = loadPerson(person) ; 
			int timeInSeconds = runnerWithResult.getTimeInSeconds() ; 
			int rankAll = runnerWithResult.getOverallRank() ;
			int rankGender = runnerWithResult.getSexRank() ;
			int rankCohort = runnerWithResult.getGroupRank() ; 
			int cohortId = findCohortId(person) ; 
			String insertResultSql = makeInsertResultSql(raceId, personId, timeInSeconds, cohortId, rankAll, rankGender, rankCohort) ;
			sqlBatch[i] = insertResultSql ; 
		}
		sqlBatch[sqlBatch.length-1] = "commit" ; 
		insertAllResults(sqlBatch) ; 
		System.out.println("loadRunnersAndResults completed. result count: " + allRunnersAndResults.size()) ; 
	}

	private int insertAllResults(String[] sqlBatch) {
		Statement insertResultStatement = null ; 
		int count = sqlBatch.length ; // Way to or need to count each row as it's inserted.
		try {
			insertResultStatement = this.databaseConnection.createStatement() ; 
			for (int i = 0; i < sqlBatch.length; i++) {
				insertResultStatement.addBatch(sqlBatch[i]) ;
			}
			System.out.println("first line of sql: " + sqlBatch[0]) ; 
			insertResultStatement.executeBatch() ; 
		}
		catch(SQLException sqlException) {
			System.out.println("insertAllResults failed.") ;
			System.out.println("failure message: \n" + sqlException.getMessage()) ;
			sqlException.printStackTrace() ; 
		}
		finally {
			try {
				insertResultStatement.close() ;
			}
			catch (SQLException exception) {
				exception.printStackTrace();
			} 
		}
		return count ;
	}

	private String makeInsertResultSql(int raceId, int personId, int timeInSeconds, int cohortId, int rankAll,
			int rankGender, int rankCohort) {
		String insertSql = "insert into running.result (raceId, personId, time, cohortId, rankAll, rankGender, rankCohort) values (" ;
		insertSql += raceId ; 
		insertSql += ", " + personId ; 
		insertSql += ", " + timeInSeconds ; 
		insertSql += ", " + cohortId ; 
		insertSql += ", " + rankAll ; 
		insertSql += ", " + rankGender ; 
		insertSql += ", " + rankCohort ; 
		insertSql += ")" ; 
		return insertSql ;
	}

	private int findCohortId(Runner person) {
		int cohortId = -1 ; 
		String key = person.getCohortKey(this.yearRun) ;  
		Cohort mappedCohort = this.cohortMap.get(key) ;
		if(mappedCohort != null) {
			cohortId = mappedCohort.getId() ; 
		}
		else {
			// Check all cohorts until you find the right one and put that in the map.
			Set<Cohort> allCohorts = this.fullResults.getRunnerCohorts() ;
			Iterator<Cohort> cohortIterator = allCohorts.iterator() ; 
			while(cohortIterator.hasNext() && cohortId == -1) {
				Cohort cohort = cohortIterator.next() ; 
				if(cohort.fitsPerson(person, this.yearRun)) {
					cohortId = cohort.getId() ; 
					this.cohortMap.put(key, cohort) ;  // Remember a given cohort could be in the map several times - once for each age they cover.
				}
			}
		}
		return cohortId ;
	}

	private int loadPerson(Runner person) {
		int personId = -1 ;
		//   Make sure that the same person isn't twice in a given race? If yes, then it's not the same person.
		// 	 Check against existing people based on age, first and last names,
		Runner existingPerson = allPeopleEver.get(person.getKey()) ;
		if(existingPerson == null) {
			Statement insertPersonStatement = null ; // Define outside try block.
			String insertPersonSql = "insert into running.person (name, town, state_code, birth_year, gender) values (" ;
			try {
				insertPersonStatement = this.databaseConnection.createStatement() ;
				insertPersonSql += stringify(person.getName()) ;
				insertPersonSql += ", " + stringify(person.getTown()) ;
				insertPersonSql += ", " + stringify(person.getState()) ;
				insertPersonSql += ", " + person.getBirthyear() ;
				String gender = "F" ; 
				if(person.isMale()) {
					gender = "M" ; 
				}
				insertPersonSql += ", " + stringify(gender) ;
				insertPersonSql +=  ")" ;
				insertPersonStatement.execute(insertPersonSql) ;
				insertPersonStatement.execute("commit") ; 
				System.out.println("Inserted person: " + person) ;
				String idSql = "select id from running.person order by id desc" ;
				ResultSet idResultSet = insertPersonStatement.executeQuery(idSql) ;
				idResultSet.next() ; // Opens the result set.
				personId = idResultSet.getInt("id") ; 
				person.setId(personId) ; 
				allPeopleEver.put(person.getKey(), person) ; 
			} catch (SQLException sqlException) {
				System.out.println("Sql error in loadPerson().") ;
				System.out.println("insertPersonSql: " + insertPersonSql) ;
				sqlException.printStackTrace();
			}
			finally {
				try {
					insertPersonStatement.close() ;
				} catch (SQLException sqlException2) {
					sqlException2.printStackTrace();
				} 
			}
		}
		else {
			personId = existingPerson.getId() ; 
			//     report/log that the person exists
			System.out.println("Already have person: " + person) ;
		}
		return personId ;
	}

	private Map<String, Runner> fetchAllPeopleInDatabase() {
		Map<String, Runner> allPeople = new HashMap<String, Runner>() ; // We know exactly how many boces codes NYS has.
		Statement fetchAllPeopleStatement = null ; 
		String allPeopleSql = "select * from running.person order by name" ; 
		int count = 0 ; 
		try {
			fetchAllPeopleStatement = this.databaseConnection.createStatement() ;
			ResultSet allPeopleResultSet = fetchAllPeopleStatement.executeQuery(allPeopleSql) ;
			while(allPeopleResultSet.next()) {
				String name = allPeopleResultSet.getString("name") ;
				String gender = allPeopleResultSet.getString("gender") ;
				int birthYear = allPeopleResultSet.getInt("birth_year") ;
				int id = allPeopleResultSet.getInt("id") ;
				String stateCode = allPeopleResultSet.getString("state_code") ;
				String town = allPeopleResultSet.getString("town") ;
				Runner person = new Runner(id, name, town, stateCode, birthYear, "M".equalsIgnoreCase(gender)) ;
				allPeople.put(person.getKey(), person) ; 
				count++ ; 
			}
		}
		catch (SQLException exception) {
			System.out.println("sql error in fetchAllPeopleInDatabase. Sql: " + allPeopleSql) ; 
			exception.printStackTrace();
		} 
		finally {
			try {
				fetchAllPeopleStatement.close() ;
			}
			catch (SQLException exception) {
				exception.printStackTrace();
			} 
		}
		System.out.println("fetchAllPeopleInDatabase completed. Found count: " + count) ;
		return allPeople ;
	}
	
	private void loadCohorts(int raceId) {
		String insertSql = "insert into running.cohort(gender, minAge, maxAge, raceId) values (? , ? , ?, ?)" ; 
		Set<Cohort> allCohorts = this.fullResults.getRunnerCohorts() ;
		Iterator<Cohort> cohortsIterator =  allCohorts.iterator() ;
		try {
			PreparedStatement insertStatement = this.databaseConnection.prepareStatement(insertSql, Statement.RETURN_GENERATED_KEYS) ;
			while(cohortsIterator.hasNext()) {
				Cohort cohort = cohortsIterator.next() ; 
				insertStatement.setString(1, cohort.getGender()) ;
				insertStatement.setInt(2, cohort.getMinAge()) ; 
				insertStatement.setInt(3, cohort.getMaxAge()) ;
				insertStatement.setInt(4, raceId) ; 
				int cohortId = -1 ; 
				insertStatement.execute() ;
				ResultSet insertResult = insertStatement.getGeneratedKeys() ; 
				insertResult.next() ;
				cohortId = insertResult.getInt(1) ;
				cohort.setId(cohortId) ; 
			}
		}
		catch (SQLException sqlException) {
			sqlException.printStackTrace() ; 
		}

	}

	private int loadRaceInformation() {
		Statement insertRaceStatement = null ; // Define outside try block.
		String insertRaceSql = "insert into running.race (distance, year, name) values (" ;
		int raceId = -1 ; 
		try {
			insertRaceStatement = this.databaseConnection.createStatement() ;
			insertRaceSql += this.raceDistance ;
			insertRaceSql += ", " + this.yearRun ;
			insertRaceSql += ", " + stringify(this.raceName) + ")" ;
			insertRaceStatement.execute(insertRaceSql) ;
			insertRaceStatement.execute("commit") ; 
			System.out.println("Inserted race: " + this.raceName + " " + this.yearRun) ;
			String idSql = "select id from running.race order by id desc" ;
			ResultSet idResultSet = insertRaceStatement.executeQuery(idSql) ;
			idResultSet.next() ; // Opens the result set.
			raceId = idResultSet.getInt("id") ; 
		} catch (SQLException sqlException) {
			System.out.println("Sql error in loadTurkeyTrotInformation().") ;
			System.out.println("insertRaceStatement: " + insertRaceStatement) ;
			sqlException.printStackTrace();
		}
		finally {
			try {
				insertRaceStatement.close() ;
			} catch (SQLException sqlException2) {
				sqlException2.printStackTrace();
			} 
		}
		return raceId ; 
	}

	public RaceDataLoader(int yearRun, boolean isTurkeyTrot, RunDataResult fullResults) {
		this.yearRun = yearRun ; 
		this.fullResults = fullResults ; 
		if(isTurkeyTrot) {
			setTurkeyTrotInformation() ; 
		}
		this.cohortMap = new HashMap<String, Cohort>() ; 
	}

	private void setTurkeyTrotInformation() {
		this.raceDistance = 8000 ; // meters of distance = 4.98 miles
		this.raceName = "Turkey Trot" ; 
	}

	private String stringify(String originalString) {
		String sqlCompliantString = originalString ;
		// Replace any internal quotes with ''
		sqlCompliantString = sqlCompliantString.replaceAll("'", "''") ; 
		sqlCompliantString = "'" + sqlCompliantString + "'"  ; 
		return sqlCompliantString ;
	}
	
	// Running project
	

}
